package com.yc.hr.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import scott.util.JdbcTemplate;
import scott.util.JdbcTemplate.Page;

public class DeptDao {
	
	/**
	 * 分页查询的方法
	 * @param dname
	 * @param loc
	 * @param pageNum  第几页
	 * @param size     每页行数
	 * @return         当前页数据
	 */
	public Page queryDnameAndLoc(String dname, String loc, int pageNum, int size){
		String sql = "select * from dept where 1=1";
		List<Object> params = new ArrayList<>();
		if(dname.isBlank() == false) {
			sql += " and dname like '%'||?||'%'";
			params.add(dname.trim());
		}
		if(loc.isBlank() == false) {
			sql += " and loc like '%'||?||'%'";
			params.add(loc.trim());
		}
		Page page = JdbcTemplate.queryPage(sql, pageNum, size, params.toArray());
		return page;
	}
	
	public List<Map<String,Object>> queryDnameAndLoc(String dname, String loc){
		String sql = "select * from dept where 1=1";
		List<Object> params = new ArrayList<>();
		if(dname.isBlank() == false) {
			sql += " and dname like '%'||?||'%'";
			params.add(dname.trim());
		}
		if(loc.isBlank() == false) {
			sql += " and loc like '%'||?||'%'";
			params.add(loc.trim());
		}
		// 执行查询
		List<Map<String,Object>> list = JdbcTemplate.queryList(sql, params.toArray());
		return list;
	}
	
	public void insert(String dname, String loc) {
		// 查询最大的部门编号+1作为新部门的编号
		String sql = "insert into dept values((select max(deptno)+1 from dept),"
				+ "?,?)";
		JdbcTemplate.update(sql, dname, loc);
	}
	
	// 查询部门名称是否已经存在
	public boolean existsByDname(String dname) {
		String sql = "select count(*) from dept where dname=?";
		BigDecimal number = JdbcTemplate.queryValue(sql, dname);
		int cnt = number.intValue();
		return cnt > 0;
	}
	
	// 修改判断同名
	public boolean existsByDname(String dname, Object deptno) {
		String sql = "select count(*) from dept where dname = ? and deptno <> ?";
		BigDecimal number = JdbcTemplate.queryValue(sql, dname, deptno);
		int cnt = number.intValue();
		return cnt > 0;
	}
	
	public void deleteByDeptno(Object deptno) {
		String sql = "delete from dept where deptno=?";
		JdbcTemplate.update(sql, deptno);
	}
	
	// 查询某个部门下面是否有员工
	public boolean existsByDeptno(Object deptno) {
		String sql = "select count(*) from emp where deptno=?";
		BigDecimal number = JdbcTemplate.queryValue(sql, deptno);
		int cnt = number.intValue();
		return cnt > 0;
	}
	
	// 根据部门编号修改部门信息
	public void updateByDeptno(Map<String,Object> dept) {
		String sql = "update dept set dname=?,loc=? where deptno=?";
		JdbcTemplate.update(sql, dept.get("DNAME")
				, dept.get("LOC")
				, dept.get("DEPTNO"));
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
